﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.DocModel.APIParam;
using HCQ2_Model.VRDD.InputDto;
using HCQ2_Model.VRDD.InputDto._0427;
using HCQ2_Model.VRDD.OutputDto._0427;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class DD_FriendApplyDAL : IDD_FriendApplyDAL
    {
        /// <summary>
        ///  获取好友申请列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<List<OutApplyFriend>> GetApplyFirend(DDBaseUser param)
        {
            sb?.Clear();
            sb.Append($@"SELECT * FROM 
            (SELECT primary_key,apply_note,apply_status,apply_id,apply_from,created_time FROM DD_FriendApply with(nolock) WHERE du_id={param.user_id})a INNER JOIN
            (SELECT user_id,user_guid as userid,user_name,user_img FROM dbo.T_User)u ON a.apply_id=u.user_id ORDER BY a.created_time DESC;");
            //return await SqlHelper.ExecuteList<OutApplyFriend>(sb.ToString());
            return await Task.Run(() => SqlHelper.ExecuteList<OutApplyFriend>(sb.ToString()));
        }
        /// <summary>
        ///  获取匹配手机通讯好友
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutFriendPhone> GetFriendByPhone(QueryFriendPhone param)
        {
            sb?.Clear();
            var userPhone = param.user_phone.Select(s => s.user_phone).ToList();
            sb.Append($@"SELECT u.*,phone_name='',du.du_name,du.city,du.LastLocationTime,
            (CASE WHEN ISNULL(dd_id,'')='' THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END)AS is_friend FROM 
            (SELECT user_id,user_guid as userid,user_name,user_phone FROM dbo.T_User with(nolock) WHERE user_phone IN('{string.Join("','", userPhone)}'))u LEFT JOIN
            (SELECT dd_id_2 AS dd_id FROM DD_1 with(nolock) WHERE dd_id_1={param.user_id} AND isfriend=1)d 
            ON u.user_id=d.dd_id inner join (select du_id,du_name,city,CONVERT(varchar(100),gps_time,120)AS LastLocationTime from dd_user)du on u.user_id=du.du_id;");
            return SqlHelper.ExecuteList<OutFriendPhone>(sb.ToString());
        }
        /// <summary>
        ///  获取我的通讯录好友列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns>OutMyFriend</returns>
        public List<OutMyFriend> GetMyFriend(DDBaseUser param)
        {
            sb?.Clear();
            if (param.stayType == 1)
            {
                sb.Append($@"select top {param.rows} 
                d.userid,user_id,user_name,user_img,user_phone,user_type,du_name,dd_line,city,sense,LastLocationTime,
                user_note,friendNote,friendDescribe,closeType,closeName,friendStatus,d.file_id as fileID,d.file_name as fileName,num,fileID,
                fileImage=(select top 1 file_url from T_DocBagFileRecord where file_id=d.file_id and file_type=0),
                aliasName=(case when d.stayType=0 then null else i.alias_name end),i.programId as programID,
                stayType=(case when d.stayType=1 then d.stayType
			     when d.stayType=0 then d.stayType
			    else -1 end),
                CONVERT(varchar(100),d.in_date,120) as stayTime 
                from(
                select *,
                CASE WHEN ISNULL(a.defaultFile,0)>0 THEN a.defaultFile 
	                when isnull(a.lastFile,0)>0 then a.lastFile
	                ELSE null
                END as fileID
                from(
			    SELECT *,ROW_NUMBER() OVER(ORDER BY f.status DESC,f.letter)AS num,
			    defaultFile=(SELECT TOP 1 file_id FROM dbo.T_VRStayUserLog WHERE user_code=f.userid AND stayDefault=1 AND stayType=1 ORDER BY in_date desc),
			    lastFile=(SELECT TOP 1 file_id FROM dbo.T_VRStayUserLog WHERE user_code=f.userid AND stayType=1 ORDER BY in_date desc)			
			    FROM ");
            }
            else if (param.stayType == 0)
            {
                sb.Append($@"select top {param.rows}
                d.userid,user_id,user_name,user_img,user_phone,user_type,du_name,dd_line,city,sense,LastLocationTime,user_note,
                friendNote,friendDescribe,
                closeType,closeName,friendStatus,d.file_id as fileID,d.file_name as fileName,num,fileID,
                fileImage=(select top 1 file_url from T_DocBagFileRecord where file_id=d.file_id and file_type=0),
                aliasName=(case when d.stayType=0 then null else i.alias_name end),i.programId as programID,
                stayType=(case when d.stayType=1 then d.stayType
			     when d.stayType=0 then d.stayType
			    else -1 end),
                CONVERT(varchar(100),d.in_date,120) as stayTime 
                from(
                select *,
                CASE WHEN ISNULL(a.defaultFile,0)>0  THEN a.defaultFile 
	                when isnull(a.lastFile,0)>0 then a.lastFile
	                when isnull(a.xuNi,0)>0 then a.xuNi
	                when isnull(a.lastXuNi,0)>0 then a.lastXuNi
	                ELSE a.lastFile
                END as fileID
                from(
			SELECT *,ROW_NUMBER() OVER(ORDER BY f.status DESC,f.letter)AS num,
			defaultFile=(SELECT TOP 1 file_id FROM dbo.T_VRStayUserLog WHERE user_code=f.userid AND stayDefault=1 AND stayType=1 ORDER BY in_date desc),
			lastFile=(SELECT TOP 1 file_id FROM dbo.T_VRStayUserLog WHERE user_code=f.userid AND stayType=1 ORDER BY in_date desc),
			xuNi=(select top 1 file_id from T_VRStayUserLog where user_code=f.userid and stayType=0 and  stayDefault=1 order by in_date desc),
			lastXuNi=(select top 1 file_id from T_VRStayUserLog where user_code=f.userid and stayType=0 order by in_date desc)
			FROM ");
            }
            else
            {
                sb.Append($@"SELECT TOP {param.rows} * FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY f.status DESC,f.letter)AS num FROM ");
            }
            sb.Append($@" (SELECT u.user_id,u.user_guid as userid,u.user_name,u.user_img,u.dd_line,u.user_note,u.duUser,d.*,
            letter=(CASE WHEN ISNULL(p.duNote,'')<>'' THEN  
	            CASE WHEN (ASCII(p.duNote) BETWEEN 48 AND 57 OR ASCII(p.duNote) BETWEEN 65 AND 123) THEN p.duNote ELSE '#' END
            WHEN ISNULL(d.duName,'')<>'' THEN  
	            CASE WHEN (ASCII(d.duName) BETWEEN 48 AND 57 OR ASCII(d.duName) BETWEEN 65 AND 123) THEN d.duName ELSE '#' END 
            WHEN ISNULL(u.duUser,'')<>'' THEN  
	            CASE WHEN (ASCII(u.duUser) BETWEEN 48 AND 57 OR ASCII(u.duUser) BETWEEN 65 AND 123) THEN u.duUser ELSE '#' END	
            ELSE '#' END), 
            p.closeType,p.closeName,p.friendNote,p.friendDescribe,p.duNote,p.status,
            (CASE WHEN d.hide_mobile>0 THEN u.user_phone ELSE '' END)AS user_phone FROM  
            (SELECT du_id,hide_mobile,user_type,dbo.GetDuName(du_id,{param.user_id},du_name,hide_name) AS du_name,city,
            LEFT(dbo.fun_getPY(du_name),1) AS duName,friendStatus=(SELECT COUNT(0) FROM dbo.DD_1 WHERE 
            dd_id_1=dd.du_id AND dd_id_2={param.user_id}),CONVERT(varchar(100),gps_time,120)AS LastLocationTime,
            sense=dbo.GetDistance({param.gps_x},{param.gps_y},gps_x,gps_y) FROM dbo.DD_User dd WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.du_name))
                sb.Append($" AND du_name LIKE '%{param.du_name}%' ");
            sb.Append($@" )d INNER JOIN
            (SELECT dd_id_2 AS dd_id,closeType,closeName,friendNote,friendDescribe,
	        LEFT(dbo.fun_getPY(friendNote),1) AS duNote,status FROM dbo.DD_1 with(nolock) WHERE 
            dd_id_1={param.user_id} ");
            if (param.closeType > -1)
            {
                if (param.closeType == 0)
                    sb.Append($" AND status=0 ");
                else if (param.closeType == 100)
                    sb.Append($" AND status=1 ");
                else
                    sb.Append($" AND closeType='{param.closeType}' ");
            }
            sb.Append($@" )p ON d.du_id=p.dd_id INNER JOIN
            (SELECT user_id,user_guid,user_name,LEFT(dbo.fun_getPY(user_name),1) AS duUser,user_img,user_phone,
            user_status as dd_line,user_note FROM dbo.T_User WHERE 1=1 ");
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (user_name LIKE '%{param.keyword}%' OR user_phone LIKE '%{param.keyword}%') ");
            if (param.stayType == 1)
            {
                sb.Append($@")u ON p.dd_id=u.user_id)f left join T_VRStayUserLog sul on f.userid=sul.user_code and stayType=1)a)d 
			left join T_DocumentInfo i on d.fileID=i.file_id where num>{(param.page - 1) * param.rows}; ");
            }
            else if (param.stayType == 0)
            {
                sb.Append($@")u ON p.dd_id=u.user_id)f			
			left join (select * from T_VRStayUserLog a inner join (select MAX(log_id) as id from T_VRStayUserLog
            group by file_id,user_code) as b
            on a.log_id=b.id) sul on f.userid=sul.user_code )a)d left join T_DocumentInfo i on d.file_id=i.file_id 
            where num>{(param.page - 1) * param.rows};");
            }
            else
            {
                sb.Append($")u ON p.dd_id=u.user_id)f) friend WHERE friend.num>{(param.page - 1) * param.rows};");
            }
            return SqlHelper.ExecuteList<OutMyFriend>(sb.ToString());
        }
        /// <summary>
        ///  获取人脉详情
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public OutConnection GetOutConnectionDetial(QueryConnectionDetial param, string userId, string ddId)
        {
            sb?.Clear();
            sb.Append($@"SELECT d.*,u.*,d1.*,friendStatus=(SELECT COUNT(0) FROM dbo.DD_1 WHERE dd_id_1={param.du_id} AND dd_id_2={param.user_id}),
            letter=(CASE WHEN ISNULL(d1.duNote,'')<>'' THEN  
	            CASE WHEN (ASCII(d1.duNote) BETWEEN 48 AND 57 OR ASCII(d1.duNote) BETWEEN 65 AND 123) THEN d1.duNote ELSE '#' END
            WHEN ISNULL(d.duName,'')<>'' THEN  
	            CASE WHEN (ASCII(d.duName) BETWEEN 48 AND 57 OR ASCII(d.duName) BETWEEN 65 AND 123) THEN d.duName ELSE '#' END 
            WHEN ISNULL(u.duUser,'')<>'' THEN  
	            CASE WHEN (ASCII(u.duUser) BETWEEN 48 AND 57 OR ASCII(u.duUser) BETWEEN 65 AND 123) THEN u.duUser ELSE '#' END	
            ELSE '#' END), 
            countFan=(select count_fan from DD_User where du_id={param.du_id}),
            starNum=(select star_num from T_User where user_id={param.du_id}),
            isStar=(select CAST(COUNT(0) as bit) from T_VLineUserUserStar where user_guid='{userId}' and star_guid='{ddId}'),
            (CASE WHEN u.dd_fans>0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END)AS du_fans FROM 
            (SELECT du_id,sense=dbo.GetDistance({param.gps_x},{param.gps_y},gps_x,gps_y),CAST(user_type AS BIT)AS user_type,city,friend_num=(
	            SELECT COUNT(0)AS num FROM (SELECT * FROM
	            (SELECT dd_id_2 AS dd_id_1 FROM DD_1 WHERE dd_id_1={param.user_id})d1 INNER JOIN
	            (SELECT dd_id_2 FROM DD_1 WHERE dd_id_1={param.du_id})d2 ON d1.dd_id_1=d2.dd_id_2)d INNER JOIN
                (SELECT user_id FROM dbo.T_User)u ON d.dd_id_1=u.user_id
            ),unread_num=(
	            SELECT COUNT(0) FROM T_VRDDCircleBaseService WHERE id NOT IN (
	            SELECT s_id FROM T_VRDDCircleReadService WHERE du_id={param.user_id})),dbo.GetDuName(du_id,{param.user_id},du_name,hide_name) AS du_name,gps_x,gps_y,du_sex,du_city,du_hometown,du_prof,du_school,count_visitor,hide_mobile,
            hide_name,hide_sex,hide_city,hide_hometown,hide_birthday,LEFT(dbo.fun_getPY(du_name),1) AS duName,
            du_friend=(SELECT COUNT(0) FROM dbo.DD_1 with(nolock) WHERE dd_id_1={param.user_id} AND dd_id_2={param.du_id}),
            du_friend2=(SELECT COUNT(0) FROM dbo.DD_2 with(nolock) WHERE dd_id_1={param.user_id} AND dd_id_2={param.du_id}),
            du_friend3=(SELECT COUNT(0) FROM dbo.DD_3 with(nolock) WHERE dd_id_1={param.user_id} AND dd_id_2={param.du_id}) 
            FROM dbo.DD_User with(nolock) WHERE du_id={param.du_id})d LEFT JOIN
            (SELECT dd_id_2,closeType,closeName,friendNote,friendDescribe,
	        LEFT(dbo.fun_getPY(friendNote),1) AS duNote FROM dbo.DD_1 WHERE dd_id_1={param.user_id} AND dd_id_2={param.du_id})d1 ON d.du_id=d1.dd_id_2 INNER JOIN
            (SELECT user_id,user_status as dd_line,user_guid AS userid,user_name,user_note,user_img,user_phone,DATEDIFF(MONTH,user_birth,GETDATE())/12 AS du_birthday,
            LEFT(dbo.fun_getPY(user_name),1) AS duUser,
            (SELECT COUNT(0) FROM dbo.DD_Fans with(nolock) WHERE du_id={param.du_id} AND fan_id={param.user_id} AND if_remove=0)AS dd_fans FROM dbo.T_User)u ON d.du_id=u.user_id;");
            return SqlHelper.ExecuteList<OutConnection>(sb.ToString()).FirstOrDefault();
        }
        /// <summary>
        ///  获取我的人脉
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="user">当前登录对象信息</param>
        /// <returns></returns>
        public List<OutMyConnection> GetMyConnection(QueryMyConnection param, DD_User user)
        {
            sb?.Clear();
            sb.Append($@"SELECT dd.*,uu.bridge_name,uu.bridge_img,letter=(CASE WHEN ISNULL(dd.duNote,'')<>'' THEN  
	            CASE WHEN (ASCII(dd.duNote) BETWEEN 48 AND 57 OR ASCII(dd.duNote) BETWEEN 65 AND 123) THEN dd.duNote ELSE '#' END
            WHEN ISNULL(dd.duName,'')<>'' THEN  
	            CASE WHEN (ASCII(dd.duName) BETWEEN 48 AND 57 OR ASCII(dd.duName) BETWEEN 65 AND 123) THEN dd.duName ELSE '#' END 
            WHEN ISNULL(dd.duUser,'')<>'' THEN  
	            CASE WHEN (ASCII(dd.duUser) BETWEEN 48 AND 57 OR ASCII(dd.duUser) BETWEEN 65 AND 123) THEN dd.duUser ELSE '#' END	
            ELSE '#' END)
            FROM (SELECT d1.*,u.*,se.voice_url,friendStatus=(CASE WHEN d.du_friend=1 THEN 1 ELSE 0 END),
            du_fans=(CASE WHEN dd_fans>0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END),
            du_friend=(CASE WHEN u.user_id={param.user_id} THEN 0 ELSE ISNULL(d.du_friend,100)END),du.user_type,du.city,du.dd_hidden,
            du.du_name,du.gps_x,du.gps_y,bridge_id,sense=dbo.GetDistance({param.gps_x},{param.gps_y},du.gps_x,du.gps_y),
	        du.du_sex,du.du_city,du.du_hometown,du.count_visitor,du.duName,
            du.du_school,du.du_city_id,du.du_hometown_id,du.du_prof FROM 
            (SELECT du_id,dd_hidden,user_type,city,dbo.GetDuName(du_id,{param.user_id},du_name,hide_name) AS du_name,gps_x,gps_y,du_school,
            du_city_id,du_hometown_id,du_prof,du_sex,du_city,du_hometown,count_visitor,LEFT(dbo.fun_getPY(du_name),1) AS duName
	        FROM dbo.DD_User with(nolock) WHERE 1=1 ");
            //关键字匹配真实姓名
            if (!string.IsNullOrEmpty(param.du_name))
                sb.Append($" AND du_name LIKE '%{param.du_name}%' ");
            //性别
            if (param.sex > 0)
                sb.Append($" AND du_sex={param.sex} ");
            //是否VIP
            if (param.user_type > 0)
                sb.Append($" AND user_type=1 ");
            sb.Append($@")du INNER JOIN  (SELECT user_id,user_guid as userid,user_name,user_img,user_note,user_status as dd_line,
	         countFan=ISNULL((select count_fan from DD_User where du_id=us.user_id),0),
             starNum=ISNULL((select star_num from T_User where user_id=us.user_id),0),
	         isStar=(select CAST(COUNT(0) as bit) from T_VLineUserUserStar where user_guid='{param.userid}'
	         AND star_guid=us.user_guid),
	         dd_fans=(SELECT COUNT(0) FROM dbo.DD_Fans with(nolock) WHERE du_id=us.user_id AND 
	         fan_id={param.user_id} AND if_remove=0),du_birthday=ISNULL(DATEDIFF(yy,us.user_birth,GETDATE()),0),
	         LEFT(dbo.fun_getPY(user_name),1) AS duUser,user_phone FROM dbo.T_User us WHERE 1=1 ");
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($" AND (user_name LIKE '%{param.keyword}%' OR user_phone LIKE '%{param.keyword}%') ");
            //登录名模糊匹配：登录名、手机号
            if (!string.IsNullOrEmpty(param.LoginName))
                sb.Append($" AND (login_name='{param.LoginName}' OR user_phone='{param.LoginName}') ");
            //是否在线
            if (param.dd_line > -1)
                sb.Append($"  AND user_status={param.dd_line } ");
            sb.Append($@")u ON du.du_id=u.user_id  LEFT JOIN 
	        (SELECT du_friend=1,case when dd_id_1={param.user_id} THEN dd_id_2 else dd_id_1 end AS dd_id,bridge_id=0 FROM
	        dbo.DD_1 with(nolock) WHERE dd_id_1={param.user_id} UNION
            SELECT du_friend=2,case when dd_id_1={param.user_id} THEN dd_id_2 else dd_id_1 end AS dd_id,
	        bridge_id=(SELECT TOP 1 du_id from DD_User with(nolock) 
            where du_id in(select case when dd_id_1=a.dd_id_1 THEN dd_id_2 else dd_id_1 end from DD_1 with(nolock) 
	        WHERE dd_id_1=a.dd_id_1 or dd_id_2=a.dd_id_1)
            and du_id in(select case when dd_id_1=a.dd_id_2 then dd_id_2 else dd_id_1 end from DD_1 with(nolock) 
	        WHERE dd_id_1=a.dd_id_2 or dd_id_2=a.dd_id_2)) 
            FROM dbo.DD_2 a with(nolock) WHERE (dd_id_1={param.user_id} OR dd_id_2={param.user_id}) 
	        UNION SELECT du_friend=3,case when dd_id_1={param.user_id} THEN dd_id_2 else dd_id_1 end AS dd_id,
	        bridge_id=0 FROM dbo.DD_3 b with(nolock) WHERE (dd_id_1={param.user_id} OR dd_id_2={param.user_id}) 
	        )d ON d.dd_id=du.du_id LEFT JOIN 
	        (SELECT dd_id_2,friendNote,friendDescribe,closeType,closeName,LEFT(dbo.fun_getPY(friendNote),1) AS duNote
	        FROM dbo.DD_1 WHERE dd_id_1={param.user_id})d1 ON d1.dd_id_2=d.dd_id LEFT JOIN 
	        (SELECT * FROM (SELECT du_id,voice_url,ROW_NUMBER() OVER(PARTITION BY du_id ORDER BY update_date DESC) AS num
	        FROM T_VRDDCircleUserService)s WHERE s.num=1)se ON se.du_id=u.user_id)dd LEFT JOIN 
	        (SELECT user_id,user_name AS bridge_name,user_img AS bridge_img FROM dbo.T_User)uu ON 
	        dd.bridge_id=uu.user_id WHERE 1=1 ");
            if (param.type == 0)
                //一度、二度、三度好友
                sb.Append($" AND dd.du_friend<100 ");
            else if (param.type == 1)
                //一度好友
                sb.Append($" AND dd.du_friend=1 ");
            else if (param.type == 2)
                //二度好友
                sb.Append($" AND dd.du_friend=2 ");
            else if (param.type == 3)
                //三度好友
                sb.Append($" AND dd.du_friend=3 ");
            //是否不看好友人脉
            if (param.hub_type == 1)
                sb.Append(" AND dd.du_friend<>2 ");//排除好友二度人脉
            //排序方式
            if (param.order_type == 3)
            {
                //按距离排序
                sb.Append(" ORDER BY dd.sense;");
            }
            else if (param.order_type == 0)
            {
                sb.Append(" ORDER BY dd.user_type desc ,dd.du_friend asc ,dd.sense asc ,letter asc");
            }
            else if (param.order_type == 2)
            {
                sb.Append(" ORDER BY dd.closeType desc,letter asc;");
            }
            else
            {
                //按好友度排序
                sb.Append(" ORDER BY letter;");
            }
            return SqlHelper.ExecuteList<OutMyConnection>(sb.ToString());
        }
        /// <summary>
        ///  获取人脉列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="user_id"></param>
        /// <returns></returns>
        public List<ConnectionListOutput> GetConnectionList(ConnectionListInput param, int user_id)
        {
            sb?.Clear();
            StringBuilder b = new StringBuilder();
            param.duidList?.ForEach(o => b.Append($"'{o.userid}',"));
            string guid = b.ToString().Trim(',');
            var userId = GetIdByGuidList(guid);
            if (string.IsNullOrEmpty(userId))
                userId = "''";
            if (param.stayType == 1)
            {
                sb.Append($@"select 
			d.userid,user_id,d.friendNote,friendDescribe,closeType,closeName,userName,userImg,userType,
            city,sense,userPhone,duName,duFriend,userNote,gpsX,gpsY,duSex,duCity,duProf,isStar,starNum,duFans,fanNum,visitorNum,
			d.file_id as fileID,d.file_name as fileName,
			fileImage=(select top 1 file_url from T_DocBagFileRecord where file_id=d.file_id and file_type=0),
			aliasName=(case when d.stayType=0 then null else i.alias_name end),i.programId as programID,
            stayType=(case when d.stayType=1 then d.stayType
			 when d.stayType=0 then d.stayType
			else -1 end),
			CONVERT(varchar(100),d.in_date,120) as stayTime 
			from(
            select *,
			CASE WHEN ISNULL(a.defaultFile,0)>0 THEN a.defaultFile 
				when isnull(a.lastFile,0)>0 then a.lastFile
				ELSE null
			END as fileID
			from(
			SELECT *,
			defaultFile=(SELECT TOP 1 file_id FROM dbo.T_VRStayUserLog WHERE user_code=f.userid AND stayDefault=1 AND stayType=1 ORDER BY in_date desc),
			lastFile=(SELECT TOP 1 file_id FROM dbo.T_VRStayUserLog WHERE user_code=f.userid AND stayType=1 ORDER BY in_date desc)			
			FROM (
            ");
            }
            else if (param.stayType == 0)
            {
                sb.Append($@"select		defaultFile,lastFile,xuNi,lastXuNi,
			d.userid,user_id,d.friendNote,friendDescribe,closeType,closeName,userName,userImg,userType,city,
            sense,userPhone,duName,duFriend,userNote
			,gpsX,gpsY,duSex,duCity,duProf,isStar,starNum,duFans,fanNum,visitorNum,
			d.file_id as fileID,d.file_name as fileName,
			fileImage=(select top 1 file_url from T_DocBagFileRecord where file_id=d.file_id and file_type=0),
			aliasName=(case when d.stayType=0 then null else i.alias_name end),i.programId as programID,
            stayType=(case when d.stayType=1 then d.stayType
			 when d.stayType=0 then d.stayType
			else -1 end),
			CONVERT(varchar(100),d.in_date,120) as stayTime 
			from(
            select *,
			CASE WHEN ISNULL(a.defaultFile,0)>0  THEN a.defaultFile 
	        when isnull(a.lastFile,0)>0 then a.lastFile
	        when isnull(a.xuNi,0)>0 then a.xuNi
	        when isnull(a.lastXuNi,0)>0 then a.lastXuNi
	        ELSE null
	        end as fileID
			from(
			SELECT *,
			defaultFile=(SELECT TOP 1 file_id FROM dbo.T_VRStayUserLog WHERE user_code=f.userid AND stayDefault=1 AND stayType=1 ORDER BY in_date desc),
			lastFile=(SELECT TOP 1 file_id FROM dbo.T_VRStayUserLog WHERE user_code=f.userid AND stayType=1 ORDER BY in_date desc),
			xuNi=(select top 1 file_id from T_VRStayUserLog where user_code=f.userid and stayType=0 and  stayDefault=1 order by in_date desc),
			lastXuNi=(select top 1 file_id from T_VRStayUserLog where user_code=f.userid and stayType=0 order by in_date desc)	
			FROM (");
            }
            sb.Append($@"SELECT * FROM (SELECT 
            duFriend=(CASE WHEN dd.duFriend IS NULL THEN 100 ELSE dd.duFriend END),dd.dd_id,dd.friendNote,
            dd.friendDescribe,dd.closeType,dd.closeName,du.*,u.* FROM 
            (SELECT user_id,user_img AS userImg,user_guid AS userID,user_name AS userName,
            user_type AS userType,user_phone AS userPhone,duFans=CAST((SELECT COUNT(0) FROM dbo.DD_Fans 
            WHERE fan_id={user_id} AND du_id=ue.user_id) AS BIT),isStar=CAST((SELECT COUNT(0) FROM dbo.T_VLineUserUserStar 
            WHERE user_guid='{param.userid}' AND star_guid=ue.user_guid) AS BIT),
            mark_star_num AS starNum,fans_num AS fanNum,
            user_note AS userNote FROM dbo.T_User ue WHERE user_id IN({userId}) OR user_id={user_id})u INNER JOIN
            (SELECT du_id,du_name AS duName,gps_x AS gpsX,gps_y AS gpsY,du_sex AS duSex,city,du_city AS 
            duCity,du_prof AS duProf,count_visitor AS visitorNum,
            sense=dbo.GetDistance(0,0,du.gps_x,du.gps_y) FROM dbo.DD_User du WHERE 1=1 ");
            if (param.gpsX > 0)
                sb.Append($" AND gps_x>{param.gpsX} ");
            if (param.gpsY > 0)
                sb.Append($" AND gps_y<{param.gpsY} ");
            sb.Append($@")du ON u.user_id=du.du_id LEFT JOIN (
            SELECT duFriend=0,dd_id={user_id},friendNote=NULL,friendDescribe=NULL,closeType=NULL,closeName=NULL UNION
            SELECT duFriend=1,dd_id_2 AS dd_id,friendNote,friendDescribe,closeType,closeName
            FROM dbo.DD_1 WHERE dd_id_1={user_id} AND isfriend=1 AND dd_id_2 IN({userId}) UNION
            SELECT duFriend=2,dd_id=(case when dd_id_1={user_id} then dd_id_2 else dd_id_1 end),friendNote=NULL,
            friendDescribe=NULL,closeType=NULL,closeName=NULL FROM dbo.DD_2 WHERE (dd_id_1={user_id} AND dd_id_2 
            IN({userId})) OR (dd_id_2={user_id} AND dd_id_1 IN({userId})) UNION
            SELECT duFriend=3,dd_id=(case when dd_id_1={user_id} then dd_id_2 else dd_id_1 end),friendNote=NULL,
            friendDescribe=NULL,closeType=NULL,closeName=NULL FROM dbo.DD_3 WHERE  (dd_id_1={user_id} AND dd_id_2 
            IN({userId})) OR (dd_id_2={user_id} AND dd_id_1 IN({userId}))
            ");
            if (param.stayType == 1)
            {
                sb.Append($@")dd ON u.user_id=dd.dd_id) df)f left join T_VRStayUserLog sul on f.userid=sul.user_code and stayType=1)a)d 
			left join T_DocumentInfo i on d.fileID=i.file_id ORDER BY duFriend;");
            }
            else if (param.stayType == 0)
            {
                sb.Append($@")dd ON u.user_id=dd.dd_id) df)f left join (select * from T_VRStayUserLog a inner join 
			(select MAX(log_id) as id from T_VRStayUserLog group by file_id,user_code) as b
            on a.log_id=b.id) sul on f.userid=sul.user_code )a)d 
            left join T_DocumentInfo i on d.fileID=i.file_id ORDER BY duFriend;");
            }
            else
            {
                sb.Append($@")dd ON u.user_id=dd.dd_id) df ORDER BY df.duFriend;");
            }


            return SqlHelper.ExecuteList<ConnectionListOutput>(sb.ToString());
        }
        /// <summary>
        ///  获取密友类别
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputMyCloseType> GetMyCloseType(int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT distinct closeName,closeType FROM dbo.DD_1 WHERE dd_id_1={user_id} and status>0 order by closeType;");
            return SqlHelper.ExecuteList<OutputMyCloseType>(sb.ToString());
        }
        /// <summary>
        ///  获取用户id
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        private string GetIdByGuidList(string guid)
        {
            string str = $"SELECT user_id FROM dbo.T_User WHERE user_guid IN({guid})";
            var user = SqlHelper.ExecuteList<UserListOutput>(str.ToString());
            StringBuilder userId = new StringBuilder();
            user?.ForEach(o => userId.Append($"{o.user_id},"));
            return userId.ToString().Trim(',');
        }
        /// <summary>
        ///  更新度度圈2度人脉
        /// </summary>
        public void UpdateDD2(int dd_id1, int dd_id2)
        {
            //SqlHelper.ExecuteNonQuery(" EXEC P_WriteDD2;");
            SqlHelper.ExecuteNonQuery($" EXEC dbo.PD_DealDD {dd_id1};");
            SqlHelper.ExecuteNonQuery($" EXEC dbo.PD_DealDD {dd_id2};");
        }
        /// <summary>
        ///  更新度度圈3度人脉
        /// </summary>
        public void UpdateDD3(int dd_id1, int dd_id2)
        {
            SqlHelper.ExecuteNonQuery($" EXEC dbo.PD_DealDD3 {dd_id1};");
            SqlHelper.ExecuteNonQuery($" EXEC dbo.PD_DealDD3 {dd_id2};");
        }
    }
}